package com.his.struts.util;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Properties;

import org.apache.log4j.Logger;
import org.apache.tomcat.dbcp.dbcp.BasicDataSource;

/**
 * 数据库连接
 * 
 * @author liumiaoyue
 * 
 */
public class DbUtil {
	Logger log = Logger.getLogger(DbUtil.class);
	Connection conn = null;
	PreparedStatement ps = null;
	ResultSet rs = null;

	/**
	 * 连接
	 */
	public Connection conn() {
		InputStream is;
		try {
			// 读取属性文件
			is = this.getClass().getClassLoader()
					.getResourceAsStream("db.properties");
			Properties pro = new Properties();
			// 加载
			pro.load(is);
			// 加载驱动
			String driver = pro.getProperty("dbDriver");
			String url = pro.getProperty("dbUrl");
			// 连接池
			BasicDataSource dbp = new BasicDataSource();
			// 设置驱动
			dbp.setDriverClassName(driver);
			dbp.setMaxWait(Long.parseLong(pro.getProperty("maxWait")));
			dbp.setMinIdle(Integer.parseInt(pro.getProperty("minIdle")));
			dbp.setMaxIdle(Integer.parseInt(pro.getProperty("maxIdle")));
			dbp.setUsername(pro.getProperty("username"));
			dbp.setPassword(pro.getProperty("password"));
			dbp.setUrl(url);
			conn = dbp.getConnection();
		} catch (Exception e) {
			log.error(e.getMessage());
		}
		return conn;
	}

	/**
	 * 增删改
	 * 
	 * @param sql
	 *            sql语句
	 * @param list
	 *            参数
	 * @return int 更新到行数
	 */
	public int executeUpdate(String sql, List<Object> list) {
		conn();
		int i = 0;
		try {
			ps = conn.prepareStatement(sql);
			// 循环添加值
			for (int j = 0; list != null && j < list.size(); j++) {
				// 设置对象
				ps.setObject(j + 1, list.get(j));
			}
			i = ps.executeUpdate();
		} catch (Exception e) {
			log.error(e.getMessage());
		}
		return i;
	}

	/**
	 * 查询
	 * 
	 * @param sql
	 *            sql语句
	 * @param list
	 *            参数
	 * @return ResultSet 数据集合
	 */
	public ResultSet executeQuery(String sql, List<Object> list) {
		try {
			// 调用连接方法
			conn();
			// 加载sql语句
			ps = conn.prepareStatement(sql);
			for (int i = 0; list != null && i < list.size(); i++) {
				// 对问号进行设置值ֵ
				ps.setObject(i + 1, list.get(i));
			}
			// 执行sql语句
			rs = ps.executeQuery();
		} catch (SQLException e) {
			log.error(e.getMessage());
		}
		return rs;
	}

	/**
	 * 关闭连接
	 */
	public void closeAll() {
		try {
			if (rs != null) {
				rs.close();
				rs = null;
			}
			if (ps != null) {
				ps.close();
				ps = null;
			}
			if (conn != null) {
				conn.close();
				conn = null;
			}
		} catch (SQLException e) {
			log.error(e.getMessage());
		}

	}
}
